﻿using Dapper;
using Entity;
using IRepository;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Repository
{
   public class CustomerRepository:ICustomerRepository
    {
        private readonly string _connectionString;

        public CustomerRepository(IConfiguration configuration)
        {
            // 从配置中读取连接字符串
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }
        
        public async Task<int> AddCustomerAsync(WMSBasicCustomer wMS_Basic_Customer)
        {
            string insertSql = @"
        INSERT INTO WMS_Basic_Customer (
            customer_code, customer_name, job_type, sources_of_customers,
            contact_person, contact_phone_number, email_address, customers_address,
            sales_department_id, account_name, taxid, bank_account,
            opening_bank, bkaddr, note, create_by, create_time,
            update_by, update_time, person_in_charge, status,annex_code
        )
        VALUES (
            @customer_code, @customer_name, @job_type, @sources_of_customers,
            @contact_person, @contact_phone_number, @email_address, @customers_address,
            @sales_department_id, @account_name, @taxid, @bank_account,
            @opening_bank, @bkaddr, @note, @create_by, @create_time,
            @update_by, @update_time, @person_in_charge, @status,@annex_code
        );
        -- 返回的结果是 int 类型
        SELECT CAST(SCOPE_IDENTITY() AS INT);
    ";

            using (var conn = new SqlConnection(_connectionString))
            {
                await conn.OpenAsync();
                // 执行后返回 int 类型的结果（即主键）
                int newCustomerId = await conn.QuerySingleAsync<int>(insertSql, wMS_Basic_Customer);
                return newCustomerId; // 明确返回 int 类型
            }
        }

        public async Task<List<WMS_Basic_Customer2>> GetAllCustomerAsync(string customer_code = null, string customer_name = null, int? status = null, int? job_type = null, int? person_in_charge = null, string contact_person = null)
        {
            string sql = @"
SELECT 
    c.customer_code,
    c.customer_name,
    ind.DictLabel AS industry_name,
    cs.DictLabel AS customer_source_name,
    c.status AS status,
    org.organization_name AS sales_dept_name,
    user_mgt.user_name AS sales_person_name,
    c.contact_person,
    c.contact_phone_number,
    c.email_address,
    c.customers_address,
    c.account_name,
    c.taxid,
    c.bank_account,
    c.opening_bank,
    c.bkaddr,
    c.create_by,
    c.create_time,
    c.annex_code
FROM 
    dbo.wms_basic_customer c
INNER JOIN 
    dbo.wms_Dictionary_data ind 
    ON c.job_type = ind.DictValue AND ind.DictTypeCode = 'Industry'
INNER JOIN 
    dbo.wms_Dictionary_data cs 
    ON c.sources_of_customers = cs.DictValue AND cs.DictTypeCode = 'CustomerSource'
INNER JOIN 
    dbo.sys_org_organizations org 
    ON c.sales_department_id = org.organization_id
INNER JOIN 
    dbo.sys_org_user_management user_mgt 
    ON c.person_in_charge = user_mgt.user_id
WHERE 
  -- 关键修改：将编号/名称的 OR 条件用括号包裹，确保与其他 AND 条件优先级正确
    (
        @customer_code IS NULL OR c.customer_code LIKE '%' + @customer_code + '%'
        OR 
        @customer_name IS NULL OR c.customer_name LIKE '%' + @customer_name + '%'
    )
    -- 其他条件用 AND 关联，且确保参数类型匹配
    AND (c.status = @status OR @status IS NULL)
    AND (c.job_type = @job_type OR @job_type IS NULL)
    AND (c.person_in_charge = @person_in_charge OR @person_in_charge IS NULL)
    AND (c.contact_person LIKE '%' + @contact_person + '%' OR @contact_person IS NULL)
    AND ( c.is_delete=0)
ORDER BY 
    c.create_time DESC;";

            using (var conn = new SqlConnection(_connectionString))
            {
                await conn.OpenAsync();
                var param = new
                {
                    customer_code,
                    customer_name,
                    status,
                    job_type,
                    person_in_charge,
                    contact_person  // 传递联系人参数
                };
                return (await conn.QueryAsync<WMS_Basic_Customer2>(sql, param)).AsList();
            }

        }

        public async Task<WMSBasicCustomer> GetCustomerByCode(string customerCode)
        {
            // SQL语句（参数化查询）
            string sql = "SELECT * FROM [dbo].[wms_basic_customer] WHERE customer_code = @CustomerCode";

            using (var connection = new SqlConnection(_connectionString))
            {
                return await connection.QueryFirstOrDefaultAsync<WMSBasicCustomer>(
                    sql,
                    new { CustomerCode = customerCode } // Dapper参数化
                );
            }
        }

        public async Task<WMS_Basic_Customer2> GetCustomerByCodeAsync(string customerCode)
        {
            // SQL查询语句：关联客户表和字典表，获取完整信息
            string sql = @"
SELECT 
    c.customer_code,           -- 客户编号
    c.customer_name,           -- 客户名称
    ind.DictLabel AS industry_name,  -- 所属行业（字典表翻译）
    cs.DictLabel AS customer_source_name,  -- 客户来源（字典表翻译）
    c.status,                  -- 状态（1=启用，2=停用）
    org.organization_name AS sales_dept_name,  -- 销售部门名称
    user_mgt.user_name AS sales_person_name,   -- 销售负责人名称
    c.contact_person,          -- 联系人
    c.contact_phone_number,    -- 联系电话
    c.email_address,           -- 电子邮箱
    c.customers_address,       -- 客户地址
    c.account_name,            -- 账号名称
    c.taxid,                   -- 纳税人识别号
    c.bank_account,            -- 银行账号
    c.opening_bank,            -- 开户银行
    c.bkaddr,                  -- 开户银行地址
    c.create_by,               -- 操作人
    c.create_time,             -- 操作时间
    c.annex_code               -- 附件编码（逗号分隔）
FROM 
    dbo.wms_basic_customer c   -- 客户主表
INNER JOIN 
    dbo.wms_Dictionary_data ind  -- 行业字典表
    ON c.job_type = ind.DictValue AND ind.DictTypeCode = 'Industry'
INNER JOIN 
    dbo.wms_Dictionary_data cs   -- 客户来源字典表
    ON c.sources_of_customers = cs.DictValue AND cs.DictTypeCode = 'CustomerSource'
INNER JOIN 
    dbo.sys_org_organizations org  -- 部门表
    ON c.sales_department_id = org.organization_id
INNER JOIN 
    dbo.sys_org_user_management user_mgt  -- 用户表
    ON c.person_in_charge = user_mgt.user_id
WHERE 
    c.customer_code = @CustomerCode;  -- 按客户编号精确查询";

            // 使用Dapper执行SQL查询
            using (var connection = new SqlConnection(_connectionString))
            {
                await connection.OpenAsync(); // 异步打开数据库连接

                // 执行查询并返回单个实体（若不存在则返回null）
                return await connection.QueryFirstOrDefaultAsync<WMS_Basic_Customer2>(
                    sql,
                    new { CustomerCode = customerCode } // 传递参数（防止SQL注入）
                );
            }

        }

        public async Task<int> UpdateCustomer(WMSBasicCustomer wMS_Basic_Customer)
        {
                string sql = @"
    UPDATE wms_basic_customer 
    SET 
        customer_name = @customer_name,
        job_type = @job_type,
        sources_of_customers = @sources_of_customers,
        contact_person = @contact_person,
        contact_phone_number = @contact_phone_number,
        email_address = @email_address,
        customers_address = @customers_address,
        sales_department_id = @sales_department_id,
        account_name = @account_name,
        taxid = @taxid,
        bank_account = @bank_account,
        opening_bank = @opening_bank,
        bkaddr = @bkaddr,
        note = @note,
        update_by = @update_by,
        update_time = @update_time,
        status = @status,
        person_in_charge = @person_in_charge,
        annex_code = @annex_code
       
    WHERE 
        customer_code = @customer_code;  -- 按客户编号（唯一标识）更新
    ";

                using (var conn = new SqlConnection(_connectionString))
                {
                    await conn.OpenAsync();
                    // 异步执行更新，返回受影响的行数（Dapper的ExecuteAsync返回int类型）
                    int affectedRows = await conn.ExecuteAsync(sql, wMS_Basic_Customer);
                    return affectedRows; // 返回受影响的行数，用于判断更新是否成功
                }
        }

        public async Task<int> UpdateIsDelete(string customerCode, string isDelete)
        {
                        string sql = @"
            UPDATE wms_basic_customer 
            SET is_delete = @is_delete
            WHERE customer_code = @customer_code;
            ";
             using (var conn = new SqlConnection(_connectionString))
              {
                   await conn.OpenAsync();
                return await conn.ExecuteAsync(sql, new { customer_code = customerCode, is_delete = isDelete });
               }
        }
    }
}
